package excel;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import po.NationalDataObj;

/**
 * @author suwy
 * @date 2014-2-12
 * @todo TODO
 */
public class Json2Exl2 {

	private static final Logger LOG = Logger.getLogger(Json2Exl2.class);
	private static String fileName = "国家数据";
	private static String filePath = "I:/data/新康界抓取数据/"+fileName;
	private static String outputFile = "i:/data/新康界抓取数据/"+fileName+"2.xlsx";
	
	private void translate() {
		StringBuilder content = new StringBuilder();
		FileOutputStream fOut = null;
		int tableDataPoint = 0;
		int indexBeginPoint = 0;
		int indexEndPoint = 0;
		try {
			Workbook workbook = new SXSSFWorkbook(2000);
			Sheet sheet = workbook.createSheet("sheet0");
			Row row = null;
			fOut = new FileOutputStream(outputFile);
			int rowNum = 0;
			for(int i = 304; i <= 641; i++) {
				content.append(readHtm(filePath + "/" + i + ".txt"));
				tableDataPoint = content.indexOf("\"tableData\":{},");
				if(tableDataPoint < 0) {
					indexBeginPoint = content.toString().indexOf("\"index\":");
					indexEndPoint = content.toString().indexOf("]", indexBeginPoint);
					JSONObject jsonObject = JSONObject.fromObject("{" + content.toString().substring(indexBeginPoint, indexEndPoint+1) + "}");
					JSONArray jsonArray = jsonObject.getJSONArray("index");
						for (int j = 0; j < jsonArray.size(); j++) {
							content.delete(0, content.length());
							content.append(jsonArray.getString(j));
							jsonObject = JSONObject.fromObject(content.toString());
							row = sheet.createRow(rowNum);
							row.createCell(0).setCellValue(jsonObject.get("id").toString().trim());
							row.createCell(1).setCellValue(jsonObject.get("name").toString().trim());
							row.createCell(2).setCellValue(jsonObject.get("id").toString().substring(0, jsonObject.get("id").toString().length()-2).trim());
							++rowNum;
						}
				}
				content.delete(0, content.length());
			}
			workbook.write(fOut);
		} catch (Exception e){
			LOG.info(e);
	} finally {
		if (null != fOut) {
			try {
				fOut.close();
				LOG.info("success export excel");
			} catch (Exception e) {
				e.printStackTrace(System.err);
			}
		}
	}
}
	
	static StringBuffer readHtm(String filePath){
		StringBuffer document = new StringBuffer();
		InputStreamReader fr = null;
		BufferedReader br = null;
		String line = null;
		try {
			fr = new InputStreamReader(new FileInputStream(filePath), "utf-8");
			br = new BufferedReader(fr);
			while ((line = br.readLine()) != null) {
				document.append(line);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (br != null) {
					br.close();
				}
				if (fr != null) {
					fr.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		LOG.info("NO."+filePath);
		return document;
	}
	
	public static void main(String[] args) {
		Json2Exl2 instance = new Json2Exl2();
		instance.translate();
	}

}
